/** 
 * @Title: IDDAO.java 
 * @Package com.zizo.autohome.socar.carid 
 * @Description: TODO
 * @author HeYan
 * @date 2017年1月18日 上午10:39:38 
 */
package com.zizo.autohome.socar.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

import com.zizo.autohome.socar.bean.BInfo;
import com.zizo.autohome.socar.bean.CInfo;
import com.zizo.autohome.socar.bean.Relate;
import com.zizo.autohome.socar.bean.RelateBrand;
import com.zizo.autohome.socar.bean.RelateCar;
import com.zizo.util.ConnectionPool;

/**
 * @ClassName: IDDAO
 * @Description: TODO
 * @author HeYan
 * @date 2017年1月18日 上午10:39:38
 * 
 */
public final class IDDAO {
	private Connection conn;

	public IDDAO(Connection conn) {
		super();
		this.conn = conn;
	}

	/**
	 * @Title: getRelateType
	 * @Description: 得到Relate表中Type的<type,typeID>
	 * @param type
	 *            b--brand c--car m--model
	 * @return
	 * @return Map<String,Integer>
	 * @author Alexis
	 */
	public Map<String, Integer> getRelateType(String type) {
		Map<String, Integer> map = new HashMap<String, Integer>();
		String sql = "SELECT DISTINCT relate_type_name,relate_type FROM socar_core.`relate` WHERE relate_type_name LIKE '"
				+ type.toLowerCase() + "%'";
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				String type_name = rs.getString(1);
				int type_id = rs.getInt(2);
				map.put(type_name, type_id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return map;
	}

	/**
	 * @Title: getRelateParam
	 * @Description: 得到Relate表中Param的<Param,ParamID>
	 * @param type
	 *            b--brand c--car m--model
	 * @return
	 * @return Map<String,Integer>
	 * @author Alexis
	 */
	public Map<String, Integer> getRelateParam(String type) {
		Map<String, Integer> map = new HashMap<String, Integer>();
		String sql = "SELECT DISTINCT relate_param_name,relate_param FROM socar_core.`relate` WHERE relate_type_name LIKE '"
				+ type.toLowerCase() + "%'";
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				String type_name = rs.getString(1);
				int type_id = rs.getInt(2);
				map.put(type_name, type_id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return map;
	}

	/**
	 * @Title: getTypeMaxID
	 * @Description: 得到最大的TypeID
	 * @return
	 * @return int
	 * @author Alexis
	 */
	public int getTypeMaxID() {
		int res = 0;
		String sql = "SELECT IFNULL(MAX(relate_type),0) FROM socar_core.relate";
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				res = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}

	/**
	 * @Title: getParamMaxID
	 * @Description: 得到Param在对应Type中的最大ParamID
	 * @return
	 * @return Map<String,Integer>
	 * @author Alexis
	 */
	public Map<String, Integer> getParamMaxID() {
		Map<String, Integer> map = new HashMap<>();
		String sql = "SELECT DISTINCT relate_type_name,MAX(relate_param) FROM relate GROUP BY relate_type";
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				map.put(rs.getString(1), rs.getInt(2));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return map;
	}

	/**
	 * @Title: insertBInfo
	 * @Description: 插入品牌的信息
	 * @param binfo
	 * @return void
	 * @author Alexis
	 */
	public void insertBInfo(BInfo binfo) {
		PreparedStatement ps = null;
		String sql = "REPLACE INTO socar_core.b_info(bid,bname,bcountry,blvl) values(?,?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, binfo.getBid());
			ps.setString(2, binfo.getBname());
			ps.setInt(3, binfo.getBcountry());
			ps.setInt(4, binfo.getBlvl());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * @Title: insertCInfo
	 * @Description: 插入车型的信息
	 * @param cinfo
	 * @return void
	 * @author Alexis
	 */
	public void insertCInfo(CInfo cinfo) {
		PreparedStatement ps = null;
		String sql = "REPLACE INTO socar_core.c_info(cid,cname,c_bid,clvl,cbt,cseg,clvl_sub,cseg_sub,coem,ctype) values(?,?,?,?,?,?,?,?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, cinfo.getCid());
			ps.setString(2, cinfo.getCname());
			ps.setInt(3, cinfo.getC_bid());
			ps.setInt(4, cinfo.getClvl());
			ps.setInt(5, cinfo.getCbt());
			ps.setInt(6, cinfo.getCseg());
			ps.setInt(7, cinfo.getClvl_sub());
			ps.setInt(8, cinfo.getCseg_sub());
			ps.setInt(9, cinfo.getCoem());
			ps.setInt(10, cinfo.getCtype());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * @Title: insertRelate
	 * @Description: 插入Relate
	 * @param relate
	 * @return void
	 * @author Alexis
	 */
	public void insertRelate(Relate relate) {
		PreparedStatement ps = null;
		String sql = "REPLACE INTO socar_core.relate(relate_type,relate_type_name,relate_param,relate_param_name) VALUES(?,?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, relate.getTypeID());
			ps.setString(2, relate.getTypeName());
			ps.setInt(3, relate.getParamID());
			ps.setString(4, relate.getParamName());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void insertRelateCar(RelateCar rc) {
		PreparedStatement ps = null;
		String sql = "REPLACE INTO relate_car(cid,a_cid) values(?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, rc.getCid());
			ps.setInt(2, rc.getA_cid());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void insertRelateBrand(RelateBrand rb) {
		PreparedStatement ps = null;
		String sql = "REPLACE INTO relate_brand(bid,a_bid) values(?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, rb.getBid());
			ps.setInt(2, rb.getA_bid());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void main(String[] args) {
		Connection conn = ConnectionPool.getConnection("socar_core", "server.so.car", "3308");
		IDDAO dao = new IDDAO(conn);
		for (String s : dao.getRelateType("c").keySet()) {
			System.out.println(s);
		}
	}
}
